<?php

/**
 * Sql语句生成辅助函数
 * @author wangjiajun
 *
 */
class SqlHelper
{
    
    public static function sqlEscape($value)
    {
        if (is_string($value)) {
            if (strtolower($value) == 'now()') {
                return $value;
            } else {
                return "'" . mysql_real_escape_string($value) . "'";
            }
        } else if (is_int($value)) {
            return $value;
        } else if (is_bool($value)) {
            return $value ? 1 : 0;
        } else {
            return $value;
        }
    }
    
    public static function getSqlInsertColumnPart($row)
    {
        return "(".implode(',', array_keys($row)).")";
    }
    
    public static function getSqlInsertValuePart($row)
    {
        foreach ($row as &$value) {
            $value = self::sqlEscape($value);
        }
        unset($value);
        return "(".implode(',', array_values($row)).")";
    }
    
    public static function getSqlSelectColumnPart($columns)
    {
        return implode(',', $columns);
    }
    
    public static function getSqlUpdateSetPart($row)
    {
        $arr = array();
        foreach ($row as $key => $value) {
            $arr[] = "$key=" . self::sqlEscape($value);
        }
        return implode(',', $arr);
    }
    
    public static function getSqlWherePart($where, $and = true)
    {
        $arr = array();
        foreach ($where as $key => $value) {
            if (is_array($value)) {
                if (isset($value['op']) && isset($value['value'])
                    && in_array($value['op'], array('<', '==', '>', '<=', '>=', '!='))) {
                    $arr[] = "{$key}{$value['op']}{$value['value']}";
                }
            } else {
                $arr[] = "$key=" . self::sqlEscape($value);
            }
        }
        $glue = $and ? ' AND ' : ' OR ';
        return implode($glue, $arr);
    }
    
    public static function getSqlOrderByPart($orderBy)
    {
        $arr = array();
        foreach ($orderBy as $key => $value) {
            $value = strtolower($value);
            if ($value == 'asc') {
                $arr[] = "$key ASC";
            } else if ($value == 'desc') {
                $arr[] = "$key DESC";
            }
        }
        return implode(',', $arr);
    }
    
    public static function getInsertSql($tableName, $row)
    {
        $columnPart = SqlHelper::getSqlInsertColumnPart($row);
        $valuePart = SqlHelper::getSqlInsertValuePart($row);
        $sql = "INSERT INTO $tableName $columnPart VALUES $valuePart";
        return $sql;
    }
    
    public static function getSelectSql($tableName, $columns = null, $where = null, 
        $orderBy = null, $offset = null, $count = null)
    {
        $sql = "SELECT";
        if (is_null($columns)) {
            $sql .= " *";
        } else {
            $sql .= " " . SqlHelper::getSqlSelectColumnPart($columns);
        }
        $sql .= " FROM $tableName";
        if (!is_null($where)) {
            $sql .= " WHERE " . SqlHelper::getSqlWherePart($where);
        }
        if (!is_null($orderBy)) {
            $sql .= " ORDER BY " . SqlHelper::getSqlOrderByPart($orderBy);
        }
        if (!is_null($offset) && !is_null($count)) {
            $offset = (int) $offset;
            $count = (int) $count;
            $sql .= " LIMIT $offset,$count";
        }
        return $sql;
    }
    
    public static function getUpdateSql($tableName, $where, $row)
    {
        $wherePart = SqlHelper::getSqlWherePart($where);
        $setPart = SqlHelper::getSqlUpdateSetPart($row);
        $sql = "UPDATE $tableName SET $setPart WHERE $wherePart";
        return $sql;
    }
    
    public static function getDeleteSql($tableName, $where)
    {
        $wherePart = SqlHelper::getSqlWherePart($where);
        $sql = "DELETE FROM $tableName WHERE $wherePart";
        return $sql;
    }
}